using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data;
using System.Text;
using System;
using System.Globalization;
using System.Web.Configuration;

/// <summary>
/// Shows duplicate resources in a grid and lets a user re-assign elements to a new resource and delete resources.
/// </summary>
public partial class DuplicateResources : System.Web.UI.Page {
    /// <summary>
    /// Index of the controls in each dynamic cell
    /// </summary>
    private static class SelectedRowControlIndexes {
        public const int txtTranslation = 1,
                         btnFindSimilar = 3,
                         btnGoogle = 4;
    }
    private const int editButtonColumNo = 3;

    private Database db;
    private SortedList<int, string> resourceIds = new SortedList<int, string>();
    private StringBuilder jQueryBuffer;

    /// <summary>
    /// Fills the raddio boxes for each language in the database
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e) {
        if(!Page.IsPostBack) {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT DISTINCT language FROM " + db.TablePrefix + "resources";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataTable dt = new DataTable();
                dt.Columns.Add("language");
                dt.Columns.Add("englishname");
                DbDataReader reader = cmd.ExecuteReader();
                string abbrev;
                while(reader.Read()) {
                    abbrev = reader.GetString(0);
                    if(abbrev.Length > 0) {
                        try {
                            dt.Rows.Add(abbrev, CultureInfo.GetCultureInfo(abbrev).EnglishName);
                        } catch {
                            dt.Rows.Add(abbrev, abbrev);
                        }
                    }
                }
                reader.Close();
                dt.DefaultView.Sort = "englishname";
                radLanguage.DataSource = dt;
                radLanguage.DataValueField = "language";
                radLanguage.DataTextField = "englishname";
                radLanguage.DataBind();
                if(radLanguage.Items.Count > 0) {
                    radLanguage.SelectedIndex = 0;
                }
                SelectLanguage(null, EventArgs.Empty);
            }
        } else {
            if(mltMain.ActiveViewIndex == 1) {
                InitializeDetailsDataSource();
            }
        }
        lblMergeError.Visible = false;
        Page.ClientScript.RegisterClientScriptInclude("json", ResolveUrl("~/js/jquery.json-1.3.min.js"));
        Page.ClientScript.RegisterClientScriptInclude("resourcejs", ResolveUrl("~/js/resources.js"));
        ScriptManager.RegisterStartupScript(this, typeof(Page), "nojs", "$(function(){ $('#" + btnRefresh.ClientID + "').remove(); });", true);
        Master.RenderJSArrayWithCliendIds(fullsizeholder);
        jQueryBuffer = new StringBuilder();
    }

    /// <summary>
    /// Closes the database connection when rendering is done.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Unload(object sender, EventArgs e) {
        if(db != null && db.Connected) {
            db.CloseConnection();
        }
    }

    /// <summary>
    /// Outputs the jQuery buffer to JavaScript
    /// Page_PreRender > Controls Pre_Render > Page_SaveStateComplete
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_SaveStateComplete(object sender, EventArgs e) {
        jQueryBuffer.Insert(0, "var prm = Sys.WebForms.PageRequestManager.getInstance(); prm.add_endRequest(function() { \n");
        jQueryBuffer.Append("});");
        ScriptManager.RegisterStartupScript(this, typeof(Page), "jquery", jQueryBuffer.ToString(), true);
    }

    /// <summary>
    /// Initializes the datasource and rebinds the grid.
    /// </summary>
    private void BindGrid() {
        InitializeResourcesDataSource();
        grdFindDuplicates.DataBind();
    }

    /// <summary>
    /// Filters duplicate resources according to language
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void SelectLanguage(object sender, EventArgs e) {
        if(radLanguage.SelectedItem != null) {
            BindGrid();
        }
    }

    /// <summary>
    /// Initializes the resources data source.
    /// </summary>
    private void InitializeResourcesDataSource() {
        switch(Constants.DatabaseInstanceType) {
            case Database.DatabaseType.MySQL:
                dsResources.SelectCommand = "SELECT r.resourceid, r.translation, COUNT(*) AS number FROM " + WebConfigurationManager.AppSettings["TablePrefix"] + "resources AS r WHERE language = ?lang GROUP BY translation HAVING COUNT(*) > 1 ORDER BY translation;";
                break;
            case Database.DatabaseType.SQLServer:
                dsResources.SelectCommand = @"SELECT translation,resourceid,number FROM
                                                (SELECT ROW_NUMBER() OVER (PARTITION BY translation ORDER BY resourceid DESC) 
                                                AS Seq,translation,resourceid,
                                                COUNT(resourceid) OVER (PARTITION BY translation) AS number
                                                FROM " + WebConfigurationManager.AppSettings["TablePrefix"] + @"resources
                                                WHERE language = @lang
                                                )t WHERE Seq=1 AND number > 1";
                break;
        }
        if(dsResources.SelectParameters.Count == 0) {
            dsResources.SelectParameters.Add("lang", TypeCode.String, radLanguage.SelectedValue);
        } else {
            dsResources.SelectParameters["lang"].DefaultValue = radLanguage.SelectedValue;
        }
    }

    /// <summary>
    /// Shows the number of items on all pages of the GridView
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.SqlDataSourceStatusEventArgs"/> instance containing the event data.</param>
    protected void dsResources_Selected(object sender, SqlDataSourceStatusEventArgs e) {
        lblNumDuplicates.Text = string.Format("{0} Duplicate Resources for {1}", e.AffectedRows, radLanguage.SelectedItem.Text);
    }

    /// <summary>
    /// Handles RowCommands from the details grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grdFindDuplicates_RowCommand(object sender, GridViewCommandEventArgs e) {
        BindGrid();
        switch(e.CommandName) {
            case "viewdetails":
                ViewDuplicates(e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Changes the page shown in the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grdFindDuplicates_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdFindDuplicates.PageIndex = e.NewPageIndex;
        BindGrid();
    }

    /// <summary>
    /// Handles sorting for the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grdFindDuplicates_Sorting(object sender, GridViewSortEventArgs e) {
        string oldExpression = grdFindDuplicates.SortExpression,
               newExpression = e.SortExpression;
        if(oldExpression.IndexOf(newExpression) < 0) {
            if(oldExpression.Length > 0) {
                e.SortExpression = newExpression + "," + oldExpression;
            } else {
                e.SortExpression = newExpression;
            }
        } else {
            e.SortExpression = oldExpression;
        }
        BindGrid();
    }

    #region View Duplicates

    /// <summary>
    /// Shows details for a specified duplicate
    /// </summary>
    /// <param name="resourceId">The resource ID of the duplicate.</param>
    private void ViewDuplicates(string resourceId) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.AddParameterWithValue("@tID", resourceId);
            string language;
            language = radLanguage.SelectedValue;
            cmd.CommandText = "SELECT translation FROM " + db.TablePrefix + "resources WHERE resourceid = @tID AND language = @lang";
            cmd.AddParameterWithValue("@lang", language);
            string duplicateResource = cmd.ExecuteScalar().ToString();
            Session["duplicateResource"] = duplicateResource;
        }
        mltMain.ActiveViewIndex = 1;
        InitializeDetailsDataSource();
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Initializes the ResourceID and main DataSources.
    /// </summary>
    private void InitializeDetailsDataSource() {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            if(string.IsNullOrEmpty(dsDuplicateDetails.SelectCommand) || resourceIds.Count < 5) {
                StringBuilder sqlBuffer = new StringBuilder();
                Dictionary<string, int> columnIndex = new Dictionary<string, int>();
                string duplicateLanguage = radLanguage.SelectedValue,
                       format = string.Empty,
                       sqlResourceId = string.Empty,
                       abbrev;
                List<string> tmpLanguages = new List<string>();
                cmd.CommandText = "SELECT DISTINCT language FROM " + db.TablePrefix + "resources";
                DbDataReader reader = cmd.ExecuteReader();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        format = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                        sqlResourceId = @"SELECT DISTINCT resourceid, 
                                            CAST(IF(CHAR_LENGTH(translation) < 15, 
                                                CONCAT(resourceid, ' - ',translation), 
                                                CONCAT(resourceid, ' - ',SUBSTRING(translation,1,12),'...')
                                                ) AS CHAR) 
                                            AS display FROM " + db.TablePrefix + "resources WHERE language = @lang ORDER BY resourceid ASC";
                        break;
                    case Database.DatabaseType.SQLServer:
                        format = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                        sqlResourceId = @"SELECT DISTINCT resourceid, display = CAST(resourceid AS VARCHAR)+' - '+
                                            CASE
                                             WHEN LEN(translation) < 15 THEN translation
                                             ELSE SUBSTRING(translation,1,12)+'...'
                                            END
                                            FROM " + db.TablePrefix + "resources WHERE language = @lang ORDER BY resourceid ASC";
                        break;
                }
                while(reader.Read()) {
                    abbrev = reader[0].ToString();
                    if(abbrev.Trim().Length > 0) {
                        sqlBuffer.AppendFormat(format, abbrev);
                        tmpLanguages.Add(abbrev);
                    }
                }
                reader.Close();
                tmpLanguages.Sort();
                tmpLanguages.Remove(duplicateLanguage);
                tmpLanguages.Insert(0, duplicateLanguage);
                string[] languages = tmpLanguages.ToArray();
                for(int i = 0; i < languages.Length; i++) {
                    columnIndex[languages[i]] = i + editButtonColumNo + 1;
                }
                grdDuplicateDetail.ColumnsGenerator = new LanguageColumnGenerator(languages);

                sqlBuffer.Remove(sqlBuffer.Length - 1, 1); // remove last comma
                string crossTabColumns = sqlBuffer.ToString();
                Session["crossTabColumns"] = crossTabColumns;
                Session["colIndexes"] = columnIndex;
                sqlBuffer.Remove(0,sqlBuffer.Length);
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                         sqlBuffer.Append(@"SELECT r.resourceid AS tid, c.comment AS resourcecomment, CAST(COUNT(e.resourceid) / (SELECT COUNT(resourceid) FROM ")
                                  .Append(db.TablePrefix).Append("resources WHERE resourceid = tid)  AS SIGNED) AS numelements, ").Append(crossTabColumns)
                                  .Append("FROM ").Append(db.TablePrefix).Append("resources AS r LEFT JOIN ").Append(db.TablePrefix).Append(@"resourcecomments AS c ON r.resourceid = c.resourceid
                                           LEFT JOIN ").Append(db.TablePrefix).Append(@"elements AS e ON r.resourceid = e.resourceid
                                           WHERE r.resourceid IN (SELECT DISTINCT resourceid FROM ").Append(db.TablePrefix).Append(@"resources WHERE language = ?lang AND translation = ?translation)
                                           GROUP BY r.resourceid");
                        break;
                    case Database.DatabaseType.SQLServer:
                        sqlBuffer.Append(@"SELECT r.resourceid AS tid, c.comment AS resourcecomment, COUNT(e.resourceid) / COALESCE(NULLIF((SELECT COUNT(r.resourceid) FROM ").Append(db.TablePrefix).Append(@"resources AS r WHERE r.resourceid = e.resourceid), 0), 1) AS numelements, ").Append(crossTabColumns)
                                 .Append("FROM ").Append(db.TablePrefix).Append("resources AS r LEFT JOIN ").Append(db.TablePrefix).Append(@"resourcecomments AS c ON r.resourceid = c.resourceid
                                          LEFT JOIN ").Append(db.TablePrefix).Append(@"elements AS e ON r.resourceid = e.resourceid
                                          WHERE r.resourceid IN (SELECT DISTINCT resourceid FROM ").Append(db.TablePrefix).Append(@"resources WHERE language = @lang AND translation = @translation)
                                          GROUP BY r.resourceid,
                                          c.comment,
                                          e.resourceid");

                        break;
                }
                dsDuplicateDetails.SelectCommand = sqlBuffer.ToString();
                grdDuplicateDetail.DataSource = dsDuplicateDetails;

                if(dsDuplicateDetails.SelectParameters.Count == 0) {
                    dsDuplicateDetails.SelectParameters.Add("lang", TypeCode.String, duplicateLanguage);
                    dsDuplicateDetails.SelectParameters.Add("translation", TypeCode.String, Session["duplicateResource"] == null ? "" : Session["duplicateResource"].ToString());
                } else {
                    dsDuplicateDetails.SelectParameters["lang"].DefaultValue = duplicateLanguage;
                    dsDuplicateDetails.SelectParameters["translation"].DefaultValue = Session["duplicateResource"] == null ? "" : Session["duplicateResource"].ToString();
                }
                cmd.CommandText = sqlResourceId;
                cmd.AddParameterWithValue("@lang", duplicateLanguage);
                reader = cmd.ExecuteReader();
                resourceIds.Clear();
                resourceIds.Add(-1, "New Resource");
                while(reader.Read()) {
                    resourceIds.Add(int.Parse(reader["resourceid"].ToString()), reader["display"].ToString());
                }
                reader.Close();
            }
        }
    }

    /// <summary>
    /// Databinds the ResourceID combobox and assigns JavaScript listeners.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewRowEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_RowDataBound(object sender, GridViewRowEventArgs e) {
        if(e.Row.RowType == DataControlRowType.DataRow) {
            DataRowView view = e.Row.DataItem as DataRowView;

            Label lblResourceIdDesc = e.Row.FindControl("lblResourceIdDesc") as Label;
            DropDownList cboResourceId = e.Row.FindControl("cboResourceID") as DropDownList;
            Button btnSave = e.Row.FindControl("btnSave") as Button;
            ImageButton btnDelete = e.Row.FindControl("btnDelete") as ImageButton;
            int numElements = int.Parse(view["numelements"].ToString());
            if(numElements == 0) {
                lblResourceIdDesc.Visible = false;
                cboResourceId.Visible = false;
                btnSave.ToolTip = "Save resource";
            } else {
                if(numElements == 1) {
                    lblResourceIdDesc.Text = "Reassign 1 element to resource";
                } else {
                    lblResourceIdDesc.Text = string.Format("Reassign {0} elements to resource", numElements);
                }
                cboResourceId.DataSource = resourceIds;
                cboResourceId.DataTextField = "Value";
                cboResourceId.DataValueField = "Key";
                cboResourceId.DataBind();
                cboResourceId.SelectedValue = view["tid"].ToString();
                btnSave.ToolTip = string.Format("Save translations and assign to {0} elements", numElements);
                btnDelete.ToolTip = "Resources cannot be deleted if there are elements using them";
                btnDelete.ImageUrl = ResolveUrl("~/images/delete_disabled.png");
            }

            // find the JS callback which is assigned to the 'Change Resource' button
            Button btnChangeResource = e.Row.FindControl("btnChangeResource") as Button;
            string jsChangeResource = ClientScript.GetPostBackClientHyperlink(btnChangeResource, "");
            cboResourceId.Attributes["onchange"] = jsChangeResource; // assign the callback to the dropdown list so a button press is simulated when the index changes

            TextBox txtResourceComment = (e.Row.FindControl("txtResourceComment") as TextBox);
            if(!String.IsNullOrEmpty(txtResourceComment.Text)) {
                e.Row.Cells[3].ToolTip = txtResourceComment.Text;
                e.Row.Cells[3].CssClass = "ssCell commentCell";
            }
        }
    }


    /// <summary>
    /// Changes the page shown in the duplicate details grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdDuplicateDetail.PageIndex = e.NewPageIndex;
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Sets up JQuery event listeners for the 'Find Resources' button
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_PreRender(object sender, EventArgs e) {
        if(grdDuplicateDetail.SelectedIndex >= 0 && grdDuplicateDetail.Rows.Count > grdDuplicateDetail.SelectedIndex) {
            GridViewRow row = grdDuplicateDetail.Rows[grdDuplicateDetail.SelectedIndex];
            Label lblResourceIdDesc = row.FindControl("lblResourceIdDesc") as Label;
            if(lblResourceIdDesc.Visible) { // if the row is invisible, the elements using this resource have been re-assigned and there is no need to register the AJAX search
                View view;
                TextBox txt;
                HyperLink btn;
                string rowId;
                for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                    rowId = row.Cells[i].ClientID;
                    view = (row.Cells[i].Controls[0] as MultiView).Views[1];
                    txt = view.Controls[1] as TextBox;
                    btn = view.Controls[3] as HyperLink;
                    Utils.AssignFindSimilarListener(jQueryBuffer, btn.ClientID, txt.ClientID, rowId, btn.NavigateUrl.Substring(1), false);
                }
            }
        }
    }

    /// <summary>
    /// Handles RowCommands from the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_RowCommand(object sender, GridViewCommandEventArgs e) {
        InitializeDetailsDataSource();
        grdDuplicateDetail.DataBind();
        GridViewRow row;  // The row which has clicked was the undatabound one, so use it's index to lookup the new, databound row
        switch(e.CommandName) {
            case "editresource": // 'Edit' button clicked
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ToggleEditControls(row, true);
                grdDuplicateDetail.SelectedIndex = row.RowIndex;
                break;
            case "canceledit":
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                ToggleEditControls(row, false);
                grdDuplicateDetail.SelectedIndex = -1;
                break;
            case "changeresource": // index of dropdownlist changed
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                string selectedResourceID = (row.Cells[editButtonColumNo].FindControl("cboResourceID") as DropDownList).SelectedValue;
                ChangeDisplayedResource(row, selectedResourceID);
                break;
            case "saveresource": // 'Save' button clicked
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                UpdateResource(row, e.CommandArgument.ToString());
                grdDuplicateDetail.SelectedIndex = -1;
                break;
            case "deleteresource":
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ConfirmDelete(row, e.CommandArgument.ToString());
                break;
            case "confirmdelete":
                DeleteResource(e.CommandArgument.ToString());
                break;
            case "showelements":
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                ShowElements(row, e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Handles the Sorting event of the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_Sorting(object sender, GridViewSortEventArgs e) {
        /*      InitializeDataSource();
              InitializeGridView();
              //grdResources.DataBind();
              string oldExpression = grdResources.SortExpression,
                     newExpression = e.SortExpression;
              if(oldExpression.IndexOf(newExpression) < 0) {
                  if(oldExpression.Length > 0) {
                      e.SortExpression = newExpression + "," + oldExpression;
                  } else {
                      e.SortExpression = newExpression;
                  }
              } else {
                  e.SortExpression = oldExpression;
              }*/
        //        grdResources.DataBind();
    }

    /// <summary>
    /// Shows the textbox controls to edit resources for a resource.
    /// </summary>
    /// <param name="row">The row for which to show the controls.</param>
    /// <param name="visible"><c>true</c> to show the controls, <c>false to hide them</c>.</param>
    private void ToggleEditControls(GridViewRow row, bool visible) {
        int oldRowActiveIndex = visible ? 0 : 1,
            newRowActiveIndex = visible ? 1 : 0;
        if(grdDuplicateDetail.SelectedIndex >= 0 && grdDuplicateDetail.Rows.Count > grdDuplicateDetail.SelectedIndex) {
            GridViewRow oldRow = grdDuplicateDetail.SelectedRow;
            for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                (oldRow.Cells[i].Controls[0] as MultiView).ActiveViewIndex = oldRowActiveIndex;
            }
        }

        MultiView mltCommands = (row.FindControl("mltActions") as MultiView);
        mltCommands.ActiveViewIndex = newRowActiveIndex;
        row.Cells[2].CssClass = "ssCell"; // remove the comment indicators
        row.Cells[3].CssClass = "ssCell";
        for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
            (row.Cells[i].Controls[0] as MultiView).ActiveViewIndex = newRowActiveIndex;
        }
        if(visible) { // fill the translator comment fields
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                int textBoxControlIndex = (row.Cells[editButtonColumNo + 1].Controls[0] as MultiView).Views[1].Controls.Count - 1;// the comments textbox is the last control in the view
                object resourceid = (mltCommands.FindControl("btnDelete") as ImageButton).CommandArgument; // don't need it as an int
                cmd.CommandText = string.Format("SELECT {0} FROM {1}resources WHERE resourceid = {2}", Session["crossTabColumns"].ToString().Replace("translation", "translatorcomment"), db.TablePrefix, resourceid);
                DbDataReader reader = cmd.ExecuteReader();
                Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
                reader.Read();
                object comment;
                for(int i = 0; i < reader.FieldCount; i++) {
                    comment = reader[i];
                    System.Diagnostics.Debug.WriteLine(i + "-" + reader.GetName(i) + "-" + comment);
                    ((row.Cells[columnIndex[reader.GetName(i).ToString()]].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = comment == null ? "" : comment.ToString();
                }
//                while(reader.Read()) {
  //                  comment = reader["translatorcomment"];
    //                ((row.Cells[columnIndex[reader["language"].ToString()]].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = comment == null ? "" : comment.ToString();
      //          }
                reader.Close();
            }
        }
    }

    /// <summary>
    /// Changes the resource selected for an element and populates available resources into their textboxes in the row.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="selectedResource">The new resource ID.</param>
    private void ChangeDisplayedResource(GridViewRow oldRow, string selectedResource) {
        GridViewRow row = grdDuplicateDetail.Rows[oldRow.RowIndex]; // the index of the old row in the new grid
        ToggleEditControls(row, true);
        DropDownList cboResourceID = (row.Cells[editButtonColumNo].FindControl("cboResourceID") as DropDownList);
        cboResourceID.SelectedValue = selectedResource;
        row.CssClass = "ssRowSelected";
        if(selectedResource == "-1") {
            for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                ((row.Cells[i].Controls[0] as MultiView).Views[1].Controls[SelectedRowControlIndexes.txtTranslation] as TextBox).Text = "";
            }
        } else {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
                cmd.CommandText = string.Format("SELECT {0} FROM {1}resources WHERE resourceid = @tID", Session["crossTabColumns"], db.TablePrefix);
                cmd.AddParameterWithValue("@tID", selectedResource);
                DbDataReader reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    object resource;
                    for(int i = 0; i < reader.FieldCount; i++) {
                        resource = reader[i];
                        ((row.Cells[columnIndex[reader.GetName(i).ToString()]].Controls[0] as MultiView).Views[1].Controls[SelectedRowControlIndexes.txtTranslation] as TextBox).Text = resource == null ? "" : resource.ToString();
                    }
                }
                reader.Close();

                // fill the translators' comment boxes
                int txtCommentControlIndex = (row.Cells[editButtonColumNo + 1].Controls[0] as MultiView).Views[1].Controls.Count - 1;// the comments textbox is the last control in the view
                cmd.CommandText = cmd.CommandText.Replace("translation", "translatorcomment");
                reader = cmd.ExecuteReader();
                reader.Read();
                object comment;
                for(int i = 0; i < reader.FieldCount; i++) {
                    comment = reader[i];
                    ((row.Cells[columnIndex[reader.GetName(i).ToString()]].Controls[0] as MultiView).Views[1].Controls[txtCommentControlIndex] as TextBox).Text = comment == null ? "" : comment.ToString();
                }
                reader.Close();

                // fill the overall resource comment
                cmd.CommandText = "SELECT comment FROM " + db.TablePrefix + "resourcecomments WHERE resourceid = @tID";
                reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    (row.Cells[editButtonColumNo].FindControl("txtResourceComment") as TextBox).Text = reader.GetString(0);
                } else {
                    (row.Cells[editButtonColumNo].FindControl("txtResourceComment") as TextBox).Text = string.Empty;
                }
                reader.Close();
            }
        }
    }

    /// <summary>
    /// Changes elements to use a new resource
    /// </summary>
    /// <param name="oldRow">The row containing the 'selected resource' dropdown box.</param>
    /// <param name="oldId">The old ID.</param>
    private void UpdateResource(GridViewRow oldRow, string oldId) {
        GridViewRow newRow = grdDuplicateDetail.Rows[oldRow.RowIndex];
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            Label lblError = grdDuplicateDetail.FooterRow.Cells[editButtonColumNo].FindControl("lblError") as Label;
            string resourceID = (oldRow.Cells[editButtonColumNo].FindControl("cboResourceId") as DropDownList).SelectedValue,
                   resourceComment = (oldRow.Cells[3].FindControl("txtResourceComment") as TextBox).Text;

            if(resourceID == "-1") {
                cmd.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                resourceID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                    break;
            }
            cmd.AddParameterWithValue("@tID", resourceID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            cmd.AddParameterWithValue("@comment", "");
            string textBoxID, language;
            Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
            for(int i = editButtonColumNo + 1; i < newRow.Cells.Count; i++) {
                textBoxID = (newRow.Cells[i].Controls[0] as MultiView).Views[1].Controls[1].UniqueID;
                foreach(string key in Request.Form.AllKeys) {
                    if(key == textBoxID && Request.Form[key].Length > 0) { // because the gridview is dynamic, the controls have to be grabbed from Request.Form
                        language = textBoxID.Substring(textBoxID.LastIndexOf("$") + 1); // the ID of the textbox has $country-CODE appended to it
                        cmd.Parameters["@trans"].Value = Request.Form[key];
                        cmd.Parameters["@lang"].Value = language;
                        string comment = Request.Form[textBoxID + "-comment"];
                        if(string.IsNullOrEmpty(comment)) {
                            cmd.Parameters["@comment"].Value = DBNull.Value;
                        } else {
                            cmd.Parameters["@comment"].Value = comment;
                        }
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            if(resourceComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE " + db.TablePrefix + "resourcecomments SET comment = @comment WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", resourceID);
                cmd.AddParameterWithValue("@comment", resourceComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE " + db.TablePrefix + "elements SET resourceid = @tID WHERE resourceid = @oldTId";
            cmd.AddParameterWithValue("@tID", resourceID);
            cmd.AddParameterWithValue("@oldTId", oldId);
            try {
                cmd.ExecuteNonQuery();
            } catch(Exception ex) {
                lblError.Text = "Error: " + ex.Message;
                lblError.Visible = true;
            }
        }
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Confirms deletion of a resource or shows an error if it is still used by elements.
    /// </summary>
    /// <param name="row">The row.</param>
    /// <param name="resourceId">The resource id.</param>
    private void ConfirmDelete(GridViewRow row, string resourceId) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT COUNT(*) FROM " + db.TablePrefix + "elements WHERE resourceid = @tId";
            cmd.AddParameterWithValue("@tId", resourceId);
            int numElements = 0;
            System.Diagnostics.Debug.WriteLine(resourceId + "-" + cmd.ExecuteScalar());
            int.TryParse(cmd.ExecuteScalar().ToString(), out numElements);
            if(numElements > 0) {
                ToggleEditControls(row, true);
                grdDuplicateDetail.SelectedIndex = row.RowIndex;
                Label lblError = row.FindControl("lblError") as Label;
                lblError.Visible = true;
                lblError.Text = "Resources cannot be deleted if there are elements using them";
            } else {
                ToggleEditControls(row, false);
                (row.FindControl("mltActions") as MultiView).ActiveViewIndex = 2;
            }
        }
    }

    /// <summary>
    /// Deletes the specified resource.
    /// </summary>
    /// <param name="resourceId">The ID of the resource to delete.</param>
    private void DeleteResource(string resourceId) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "DELETE FROM " + db.TablePrefix + "resources WHERE resourceid = @tId";
            cmd.AddParameterWithValue("@tId", resourceId);
            cmd.ExecuteNonQuery();
        }
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Shows the elements used by a resource.
    /// </summary>
    /// <param name="newRow">The row in the GridView.</param>
    /// <param name="visible">1 if the elements panel should be visible, otherwise false.</param>
    private void ShowElements(GridViewRow newRow, string visible) {
        Panel pnlElementDetail = newRow.FindControl("pnlElementDetail") as Panel;
        if(visible == "1") { // needed because the button ID is used to store the resourceID
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            ImageButton imgShowDetails = newRow.FindControl("imgShowDetails") as ImageButton;
            imgShowDetails.ImageUrl = ResolveUrl("~/images/arrow_up.gif");
            imgShowDetails.CommandArgument = "0";
            imgShowDetails.ToolTip = "Hide elements";
            using(Database.Command cmd = db.CreateCommand()) {
                StringBuilder sqlBuffer = new StringBuilder("SELECT a.displayname AS app, b.name AS bundle, e.elementname AS element FROM ")
                              .Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("bundles as b ON bundle = b.id INNER JOIN ").Append(db.TablePrefix)
                              .Append("applications AS a ON a.id = b.application WHERE resourceid = @tId ORDER BY displayname, bundle, elementname");
                cmd.CommandText = sqlBuffer.ToString();
                cmd.AddParameterWithValue("@tId", (newRow.FindControl("chkSelect") as CheckBox).Text);
                DbDataReader reader = cmd.ExecuteReader();
                SortedDictionary<string, SortedDictionary<string, List<string>>> elements = new SortedDictionary<string, SortedDictionary<string, List<string>>>(); // application, bundle, element
                string app,
                       bundle,
                       element,
                       lastApp = string.Empty,
                       lastBundle = string.Empty,
                       lastElement = string.Empty;
                BulletedList lst = null;
                while(reader.Read()) {
                    app = reader["app"].ToString();
                    bundle = reader["bundle"].ToString();
                    element = reader["element"].ToString();
                    if(app != lastApp) {
                        Literal appHeading = new Literal();
                        appHeading.Text = "<strong>" + app + "</strong>";
                        pnlElementDetail.Controls.Add(appHeading);
                        lst = new BulletedList();
                        pnlElementDetail.Controls.Add(lst);
                        lastApp = app;
                    }
                    lst.Items.Add(new ListItem(bundle + " - " + element));
                }
                pnlElementDetail.Visible = true;
            }
        }
    }

    /// <summary>
    /// Switches back to the 'Find dupllicates' form, retaining the selected values.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkNavDuplicateResources_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 0;
        BindGrid();
    }

    /// <summary>
    /// Expands/contracts the grid to fill the whole window
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkExpandScreen_Click(object sender, EventArgs e) {
        if(fullsizeholder.Attributes["class"] == "ssFullSize") {
            lnkExpandScreen.Text = "Expand to fill window";
            lnkExpandScreen.CssClass = "lnkExpand";
            fullsizeholder.Attributes["class"] = "ssContracted";
        } else {
            lnkExpandScreen.Text = "Contract to normal size";
            lnkExpandScreen.CssClass = "lnkContract";
            fullsizeholder.Attributes["class"] = "ssFullSize";
        }
    }

    #endregion

    #region Merging

    /// <summary>
    /// Checks all boxes in the duplicate details GridView. This is overridden by a JS listener which interrupts the click
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnSelectAll_Click(object sender, EventArgs e) {
        GridViewRowCollection oldRows = grdDuplicateDetail.Rows;
        InitializeDetailsDataSource();
        grdDuplicateDetail.DataBind();
        foreach(GridViewRow row in oldRows) {
            if(row.RowType == DataControlRowType.DataRow) {
                CheckBox chkSelect = grdDuplicateDetail.Rows[row.RowIndex].FindControl("chkSelect") as CheckBox;
                chkSelect.Checked = true;
            }
        }
    }

    /// <summary>
    /// Adds a JQuery listener for the 'Select All' button
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnSelectAll_PreRender(object sender, EventArgs e) {
        StringBuilder buffer = new StringBuilder(@"$('#").Append(btnSelectAll.ClientID).Append(@"').click(
             function() {
                 $(""INPUT[type='checkbox']"").attr('checked', 'true');
                return false;
             });");
        ClientScript.RegisterStartupScript(typeof(Page), "checkall", buffer.ToString(), true);
    }

    /// <summary>
    /// Merges the selected translations
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnMerge_Click(object sender, EventArgs e) {
        List<string> iDsToMerge = new List<string>();
        foreach(GridViewRow row in grdDuplicateDetail.Rows) {
            if(row.RowType == DataControlRowType.DataRow) {
                CheckBox chkSelect = row.FindControl("chkSelect") as CheckBox;
                if(chkSelect.Checked) {
                    try {
                        iDsToMerge.Add(chkSelect.Text);
                    } catch { }
                }
            }
        }
        if(iDsToMerge.Count < 2) {
            lblMergeError.Text = "No translations were selected";
            lblMergeError.Visible = true;
        } else {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            Dictionary<string, string> mergedResources = new Dictionary<string, string>();
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "resources WHERE" + db.GenerateInClause("resourceid", iDsToMerge.ToArray(), false);
                DbDataReader reader = cmd.ExecuteReader();
                string language;
                while(reader.Read()) {
                    language = reader["language"].ToString();
                    if(!mergedResources.ContainsKey(language)) {
                        mergedResources.Add(language, reader["translation"].ToString());
                    }
                }
                reader.Close();

                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation) VALUES (@tID, @lang, @trans) ON DUPLICATE KEY UPDATE translation = @trans";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @trans WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation) VALUES (@tID, @lang, @trans)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", iDsToMerge[0]); // merge all into the first ID which was selected, re-assign the rest to this ID later
                cmd.AddParameterWithValue("@lang", "");
                cmd.AddParameterWithValue("@trans", "");
                BulletedList lstErrors = new BulletedList();
                foreach(KeyValuePair<string, string> translation in mergedResources) {
                    cmd.Parameters["@lang"].Value = translation.Key;
                    cmd.Parameters["@trans"].Value = translation.Value;
                    try {
                        cmd.ExecuteNonQuery();
                    } catch(Exception ex) {
                        lstErrors.Items.Add(string.Format("Failed to merge translation for {0} - '{1}': {2}", translation.Key, translation.Value, ex.Message));
                    }
                }
                if(lstErrors.Items.Count > 0) {
                    plcResults.Controls.Add(new LiteralControl("<div class=\"errorDiv\">"));
                    plcResults.Controls.Add(new LiteralControl("<h2>Error</h2>"));
                    plcResults.Controls.Add(lstErrors);
                    plcResults.Controls.Add(new LiteralControl("<p>Errors encountered, skipping re-assigning elements</p>"));
                    plcResults.Controls.Add(new LiteralControl("</div>"));
                    mltMain.ActiveViewIndex = 2;
                } else {
                    cmd.CommandText = "UPDATE " + db.TablePrefix + "elements SET resourceid = @tID WHERE resourceid = @oldTId";
                    cmd.Parameters["@tID"].Value = iDsToMerge[0];
                    cmd.AddParameterWithValue("@oldTId", "");
                    for(int i = 1; i < iDsToMerge.Count; i++) {
                        cmd.Parameters["@oldTId"].Value = iDsToMerge[i];
                        try {
                            cmd.ExecuteNonQuery();
                        } catch(Exception ex) {
                            lstErrors.Items.Add(string.Format("Failed to re-assign elements from translation #{0} to #{1}: {2}", iDsToMerge[0], iDsToMerge[i], ex.Message));
                        }
                    }
                    if(lstErrors.Items.Count > 0) {
                        plcResults.Controls.Add(new LiteralControl("<div class=\"errorDiv\">"));
                        plcResults.Controls.Add(new LiteralControl("<h2>Error</h2>"));
                        plcResults.Controls.Add(lstErrors);
                        plcResults.Controls.Add(new LiteralControl("<p>Errors encountered, skipping re-assigning elements</p>"));
                        plcResults.Controls.Add(new LiteralControl("</div>"));
                        mltMain.ActiveViewIndex = 2;
                    }
                }
            }
        }
        InitializeDetailsDataSource();
        grdDuplicateDetail.DataBind();
    }

    #endregion
}
